<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE sqlMap PUBLIC "-//ibatis.apache.org//DTD SQL Map 2.0//EN" "http://ibatis.apache.org/dtd/sql-map-2.dtd" >
<sqlMap namespace="com.uc.training.smadmin.bd.dao.BannerDao">

	<!-- 前台获取图片 -->
	<select id="getBannerList" resultClass="com.uc.training.smadmin.bd.re.BannerRE">
		 SELECT
        s.id AS id,
        s.image_url AS imageUrl,
        s.url AS url
        FROM t_bd_banner s
        WHERE s.type = 1 AND s.is_show = 1
        ORDER BY s.sort_num ASC
	</select>

	<!-- 获取banner数量 -->
	<select id="getAdminBannerCount" resultClass="java.lang.Long" parameterClass="com.uc.training.smadmin.bd.vo.AdminBannerListVO">
		SELECT
		count(1)
		FROM t_bd_banner s
		<dynamic prepend="where">
			<isNotEmpty property="name" prepend="and">
				s.name LIKE concat("%",#name#,"%")
			</isNotEmpty>
			<isNotEmpty property="isShow" prepend="and">
				s.is_show = #isShow#
			</isNotEmpty>
		</dynamic>
	</select>

	<!-- 后台获取广告图片 -->
	<select id="getAllBannerList" resultClass="com.uc.training.smadmin.bd.re.AdminBannerListRE" parameterClass="com.uc.training.smadmin.bd.vo.AdminBannerListVO">
		 SELECT
		 s.id AS id,
		 s.name AS name,
		 s.type AS type,
		 s.sort_num AS sortNum,
		 s.image_url AS imageUrl,
		 s.url AS url,
		 s.is_show AS isShow,
		 s.clicks AS clicks,
		 s.description AS description,
		 s.create_emp AS createEmp,
		 s.create_time AS createTime,
		 s.modify_emp AS modifyEmp,
		 s.modify_time AS modifyTime
		FROM
		t_bd_banner  s
		<dynamic prepend="where">
			<isNotEmpty property="name" prepend="and">
				s.name LIKE concat("%",#name#,"%")
			</isNotEmpty>
			<isNotEmpty property="isShow" prepend="and">
				s.is_show = #isShow#
			</isNotEmpty>
		</dynamic>
		ORDER BY s.id ASC
		<isNotNull property="offset">
			limit #offset#, #pageSize#
		</isNotNull>
	</select>

	<!-- 插入 -->
	<insert id="insertBanner" parameterClass="com.uc.training.smadmin.bd.model.Banner">
		 insert into t_bd_banner
			 (name, type, sort_num, image_url, url,is_show, clicks, description, create_emp,modify_emp)
		  values (#name#,#type#,#sortNum#,#imageUrl#,#url#,#isShow#,0,#description#,#createEmp#,#modifyEmp#)
	<selectKey keyProperty="id">
		select LAST_INSERT_ID() AS id
	</selectKey>
	</insert>

	<!-- 更新 -->
	<update id="updateBanner" parameterClass="com.uc.training.smadmin.bd.model.Banner">
		 update t_bd_banner set
	   `name` = #name#, `type` = #type#, sort_num = #sortNum#, image_url = #imageUrl#, url = #url#,is_show=#isShow#, description = #description#, modify_emp = #modifyEmp#
		where id=#id#
	</update>
	<!--根据主键id删除图片-->
	<delete id="deleteBannerById" parameterClass="Long">
		DELETE FROM t_bd_banner WHERE id = #id#
	</delete>

	<!--增加点击量-->
	<update id="insertClick" parameterClass="Long">
		UPDATE t_bd_banner set clicks = clicks+1 where id = #id#
	</update>
</sqlMap>